package service.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jndi.Jndi;
import pojo.Customer;
import service.CustomerManager;

public class CustomerManagerImpl implements CustomerManager {

	@Override
	public int addCustomer(Customer c,Connection conn) {
		int m=0;
		String favs;
		String sql = "insert into customer (id,cname,salary,birth,sex,favs) values(seq02.nextval,?,?,?,?,?)";
		try {			
			PreparedStatement pst = conn.prepareStatement(sql);			
			pst.setString(1, c.getCname());
			pst.setDouble(2, c.getSal());
			pst.setDate(3, new java.sql.Date(c.getBirth().getTime()));
			pst.setBoolean(4, c.isSex());
			favs="";
			for (int i = 0; i < c.getFavs().length; i++) {
				favs=favs+c.getFavs()[i]+";";
			}
			pst.setString(5, favs);
			m=pst.executeUpdate();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return m;
	}

	@Override
	public List<Customer> findAllCustomers(Connection conn) {
		List<Customer> list=new ArrayList<Customer>();
		String sql="select * from customer";
//		Connection conn=Jndi.getConnection();
		ResultSet rs=null;
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			rs=pst.executeQuery();	
			while (rs.next()) {
				Customer c=new Customer();
				c.setCname(rs.getString("cname"));
				c.setBirth(rs.getDate("birth"));
				c.setId(rs.getInt("id"));
				c.setSal(rs.getDouble("salary"));
				c.setSex(rs.getBoolean("sex"));
				c.setFavs(rs.getString("favs").split(";"));
				list.add(c);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return list;
	}

	
	@Override
	public int deleteCustomer(String cname,Connection conn) {
		int m=0;
		String sql = "delete from customer where cname=?";
//		Connection conn=Jndi.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, cname);
			m=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return m;
		
	}

	@Override
	public int updateCustomer(Customer c,Connection conn,int id) {
		int m=0;
		String favs=null;
		String sql = "update from customer set cname=?,salary=?,birth=?,sex=?,favs=? where id=?";
//		Connection conn=Jndi.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, c.getCname());
			pst.setDouble(2, c.getSal());
			pst.setDate(3, new Date(c.getBirth().getTime()));
			pst.setBoolean(4, c.isSex());
			favs=c.getFavs()[0];
			for (int i = 1; i < c.getFavs().length; i++) {
				favs=favs+";"+c.getFavs()[i];
			}
			pst.setString(5, favs);
			pst.setInt(6, id);
			m=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return m;
	}

	@Override
	public String findFavs(String cname, Connection conn) {
		String sql="select favs from customer where cname=?";
		ResultSet rs=null;
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, cname);
			rs=pst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String favs=null;
		try {
			if (rs.next()) {
				favs = rs.getString("favs");
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return favs;
	}

	@Override
	public Customer findOne(String cname, Connection conn) {
		String sql="select * from customer where cname=?";
		ResultSet rs=null;
		Customer c=null;
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, cname);
			rs=pst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (rs.next()) {
				c=new Customer(rs.getInt("id"), rs.getString("cname"), rs.getDouble("salary"), rs.getBoolean("sex"), rs.getDate("birth"), rs.getString("favs").split(";"));				
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return c;
	}

}
